/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fr.p10.miage.gestionnote.modelservice;

import fr.p10.miage.gestionabsence.model.Connection;
import fr.p10.miage.gestionabsence.model.SessionUtilisateur;
import fr.p10.miage.gestionabsence.model.Utilisateur;
import fr.p10.miage.gestionnote.model.Controle;
import fr.p10.miage.gestionnote.model.Ec;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author gensky
 */
public class EcServices extends UtilService
{
    private static Connection cx;
    
    public static List<Controle> getControles(int idPromotion,int idEc,boolean etudiant,int idEtudiant) throws SQLException
    {
        List<Controle> listeControles = new ArrayList<Controle>();

        // Filtrer aussi par idPromotion
        String sql = "SELECT *\n" +
                    "FROM controle c\n" +
                    "WHERE c.idec ="+idEc+"\n"+
                    "AND c.idp="+idPromotion+""
                    ;
        if(etudiant)
        {
                sql+= "\nAND c.publier=1";
        }

        PreparedStatement pst = cx.getBDD().prepareStatement(sql);

        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            while (resultat.next()) {
                
                Controle controletmp;
                        
                if(etudiant)
                {
                      controletmp=  new Controle(
                        resultat.getInt("idcontrole"),
                        idPromotion,
                        idEc,
                        resultat.getString("label"),
                        resultat.getFloat("coefficient"),
                        resultat.getInt("echelle"),
                        resultat.getBoolean("publier"),
                        resultat.getInt("type"),
                        idEtudiant
                        );
                }
                else
                {
                    controletmp = new Controle(
                        resultat.getInt("idcontrole"),
                        idPromotion,
                        idEc,
                        resultat.getString("label"),
                        resultat.getFloat("coefficient"),
                        resultat.getInt("echelle"),
                        resultat.getBoolean("publier"),
                        resultat.getInt("type")
                        );
                }
                listeControles.add(controletmp);
            }
            resultat.close();
        }

        pst.close();
        return listeControles;
    }
    
    public static Ec getEcById(int idEc)
    {
        return null;
    }

    public static String getCommentaire(int idec,int idp,int idEtudiant) throws SQLException, Exception {
        String commentaire ="-";
        
        String sql = "SELECT ece.commentaire\n" +
                    "FROM ec, etudiant e, promotion p, ec_commentaire_etudiant ece\n" +
                    "WHERE ece.idetudiant = e.id\n" +
                    "AND ece.idec = ec.idec\n" +
                    "AND ece.idpromotion = p.idp\n" +
                    "AND ece.idetudiant = "+/*EcServices.getIdEtudiant()*/idEtudiant+"\n" +
                    "AND ece.idec = "+idec+"\n" +
                    "AND ece.idpromotion = "+idp+"";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql);

        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (!resultat.next())
            {
                
            }else {
                if(!resultat.getString("commentaire").equals("") || resultat.getString("commentaire") != null)
                    commentaire = resultat.getString("commentaire");
            }
            resultat.close();
        }
        pst.close();
        return commentaire;
    }

    public static boolean setPublier(int idec, int idF, int idP) throws SQLException {
        boolean publier = false;
        
        String sql = "SELECT publier\n" +
                    "FROM ec_publier\n" +
                    "WHERE idec = " + idec + "\n " +
                    "AND idf = " + idF + "\n " +
                    "AND idp = " + idP + "";
        
        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
//            if (!resultat.next())
//            {
//                
//            }else {
            if (resultat.first() == true) {
                if(resultat.getBoolean("publier"))
                {
                   publier = resultat.getBoolean("publier");
                }
            }
          
//            }
            resultat.close();
        }
        pst.close();
        
        return publier;
    }

}
